from django.http import HttpResponse
from django.shortcuts import render

from app01.models import *


# Create your views here.
def add(request):
    """
    绑定关系
    :param request:
    :return:
    """
    # pub = Publish.objects.create(name='人民出版社', email='123@qq.com', city='北京')
    # 为 Book 表绑定出版社 - Book vs Publish 多对一
    # ----- 绑定一对多的关系 -----
    # 方式1
    # book_obj = Book.objects.create(title='红楼梦', price=100, publishDate='2012-12-12', publish_id=1)
    # print(book_obj.title)
    # 方式2
    # pub_obj = Publish.objects.filter(nid='1').first()  # [0]
    # book_obj = Book.objects.create(title='三国演义', price=100, publishDate='2011-11-11', publish=pub_obj)
    # print(book_obj.title)
    # print(book_obj.price)
    # print(book_obj.publishDate)
    # print(book_obj.publish)  # 与这本书籍关联的出版社对象
    # print(book_obj.publish_id)

    # 查询西游记的出版社的邮箱
    # book_obj = Book.objects.filter(title='西游记').first()
    # print(book_obj.publish.email)

    # ----- 绑定多对多的关系 -----
    # book_obj = Book.objects.create(title='金瓶梅', price=100, publishDate='2012-12-12', publish_id=1)
    # egon = Author.objects.get(nid=2)
    # alex = Author.objects.get(name='alex')
    # 绑定多对多关系的API
    # book_obj.authors.add(alex, egon)
    # 等效于 book_obj.authors.add(1, 2)
    # 等效于 book_obj.authors.add(*[1, 2])  # 语法：给函数传列表到未知参数，需要前缀*

    # 解除多对多关系的API
    book_obj = Book.objects.filter(nid=5).first()
    # book_obj.authors.remove(2)
    # 等效于 book_obj.authors.remove(*[2,])  # 语法：给函数传列表到未知参数，需要前缀*
    # 全部清除 book_obj.authors.clear()

    print(book_obj.authors.all())  # 返回 queryset
    # 查询主键为5的书籍的所有作者的名字
    ret = book_obj.authors.all().values('name')
    print(ret)
    return HttpResponse('OK')


def query(request):
    """
    跨表查询
      1 基于对象的跨表查询
      2 基于双下划线的跨表查询
      3 聚合和分组查询
      4 F 与 Q 查询
    :param request:
    :return:
    """
    # ----- 基于对象（子查询）的跨表查询 -----
    # 一对多的正向查询：查询金瓶梅这本书的出版社的名字
    # book_obj = Book.objects.filter(title='金瓶梅').first()
    # print(book_obj.publish)  # 与这本书关联的出版社对象
    # print(book_obj.publish.name)

    # 一对多的反向查询：查询人民出版社出版过的书籍的名称
    # publish = Publish.objects.filter(name='人民出版社').first()
    # ret = publish.book_set.all()
    # print(ret)

    # 多对多的正向查询：查询金瓶梅这本书的作者的名字
    # book_obj = Book.objects.filter(title='金瓶梅').first()
    # author_list = book_obj.authors.all()  # queryset对象 [author_obj1, author_obj2,...]
    # for author in author_list:
    #     print(author.name)

    # 多对多的反向查询：查询alex出版过的所有书籍的名字
    # alex = Author.objects.filter(name='alex').first()
    # book_list = alex.book_set.all()
    # for book in book_list:
    #     print(book.title)

    # 一对一的正向查询：查询alex的手机号
    # alex = Author.objects.filter(name='alex').first()
    # print(alex.authorDetail.telephone)

    # 一对一的反向查询：查询手机号为18611112222的作者的名字和年龄
    # ad = AuthorDetail.objects.filter(telephone='18611112222').first()
    # print('name: {} age:{}'.format(ad.author.name, ad.author.age))

    # ----- 基于双下划线的跨表查询（join查询） -----
    # 不区分一对多、多对多、一对一：正向查询按字段，反向查询按表名小写，用来告诉ORM引擎join哪张表
    # 一对多查询：查询金瓶梅这本书的出版社的名字
    # 方式1
    # ret = Book.objects.filter(title='金瓶梅').values('publish__name')
    # print(ret)  # <QuerySet [{'publish__name': '南京出版社'}]>
    # 方式2
    # ret = Publish.objects.filter(book__title='金瓶梅').values('name')
    # print(ret)

    # 多对多查询：查询金瓶梅这本书的所有作者的名字
    # 方式1
    # 通过Book表join与其关联的Author表，属于正向查询：
    # 按字段authors通知ORM引擎join Book_authors表和Author表
    # SELECT `app01_author`.`name` FROM `app01_book` LEFT OUTER JOIN `app01_book_authors`
    # ON (`app01_book`.`nid` = `app01_book_authors`.`book_id`)
    # LEFT OUTER JOIN `app01_author`
    # ON (`app01_book_authors`.`author_id` = `app01_author`.`nid`)
    # WHERE `app01_book`.`title` = '金瓶梅' LIMIT 21; args=('金瓶梅',)
    # ret = Book.objects.filter(title='金瓶梅').values('authors__name')
    # print(ret)
    # 方式2
    # 通过Author表join与其关联的Book表，属于反向查询：
    # 按表名小写book通知ORM引擎join Book_authors表和Book表
    # SELECT `app01_author`.`name` FROM `app01_author` INNER JOIN `app01_book_authors`
    # ON (`app01_author`.`nid` = `app01_book_authors`.`author_id`)
    # INNER JOIN `app01_book` ON (`app01_book_authors`.`book_id` = `app01_book`.`nid`)
    # WHERE `app01_book`.`title` = '金瓶梅' LIMIT 21; args=('金瓶梅',)
    # ret = Author.objects.filter(book__title='金瓶梅').values('name')
    # print(ret)

    # 一对一的正向查询：查询alex的手机号
    # 方式1
    # 通过Author表join与其关联的AuthorDetail表，属于正向查询:
    # 按字段authorDetail通知ORM引擎join AuthorDetail表
    # ret = Author.objects.filter(name='alex').values('authorDetail__telephone')
    # print(ret)
    # 方式2
    # 通过AuthorDetail表join与其关联的Author表，属于反向查询:
    # 按表名小写author通知ORM引擎join Author表
    # ret = AuthorDetail.objects.filter(author__name='alex').values('telephone')
    # print(ret)

    # 进阶练习：连续跨表
    # 练习：手机号以186开头的作者出版过的所有书籍名称以及出版社名称
    # 方式1
    # 通过Book表join AuthorDetail表，因为Book表与AuthorDetail表无关联，所以必须连续跨表
    # ret = Book.objects.filter(authors__authorDetail__telephone__startswith='186').values('title','publish__name')
    # print(ret)
    # 方式2
    # 通过Author表join Publish表，因为Author表与Publish表无关联，所以必须连续跨表
    # ret = Author.objects.filter(authorDetail__telephone__startswith='186').values('book__title', 'book__publish__name')
    # print(ret)

    # ----- 聚合与分组查询 -----
    # ----- 聚合 aggregate：返回值是一个字典，不再是 queryset
    # 查询所有书籍的平均价格
    from django.db.models import Avg, Max, Min, Count
    # ret = Book.objects.all().aggregate(Avg('price'))
    # print(ret)  # {'price__avg': 100.0}
    # ret = Book.objects.all().aggregate(avg_pirce=Avg('price'), max_price=Max('price'))
    # print(ret)  # {'avg_pirce': 100.0, 'max_price': Decimal('100.00')}

    # ----- 分组 annotate：返回值依然是 queryset
    # 单表分组查询
    # 示例1
    # 查询每一个部门的名称以及员工的平均薪水
    # select dep,Avg(salary) from emp group by dep
    # ret = Emp.objects.values('dep').annotate(avg_salary=Avg('salary'))
    # print(ret)
    # 单表分组查询的ORM语法：单表模型.objects.values('group by的字段').annotate(聚合函数('统计字段'))
    # 在单表分组下，按照主键进行group by是没有任何意义的。

    # 示例2
    # 查询每一个省份的名称以及员工数
    # ret = Emp.objects.values('province').annotate(cnt=Count('id'))
    # print(ret)
    # ret = Emp.objects.all()  # select * from emp
    # print(ret)

    # ----- 多表分组查询：
    # 示例1：查询每一个出版社所出版的书籍的数目
    # Book.objects.values('publish_id').annotate(Count('id'))
    # 查询每一个出版社的名称以及所出版的书籍的数目
    # SQL for JOIN: select * from app01_book INNER JOIN app01_publish on app01_book.publish_id=app01_publish.nid
    # SQL for 分组查询：
    # select app01_publish.name,Count(app01_book.title) from app01_book INNER JOIN app01_publish
    # on app01_book.publish_id=app01_publish.nid
    # group by app01_publish.nid
    # 如何利用ORM语法进行跨表分组查询
    # ret = Publish.objects.values('nid').annotate(c=Count('book__title'))
    # print(ret)  # <QuerySet [{'nid': 1, 'c': 3}, {'nid': 2, 'c': 1}]>
    # ret = Publish.objects.values('name').annotate(c=Count('book__title'))
    # print(ret)  # <QuerySet [{'name': '人民出版社', 'c': 3}, {'name': '南京出版社', 'c': 1}]>
    # ret = Publish.objects.values('nid').annotate(c=Count('book__title')).values('name','c')
    # print(ret)  # <QuerySet [{'name': '人民出版社', 'c': 3}, {'name': '南京出版社', 'c': 1}]>

    # 示例2：查询每一个作者的名字及其出版过的书籍的最高价格
    # select app01_author.name, Max(app01_book.price) from app01_book INNER JOIN app01_book_authors
    # ON app01_book.nid=app01_book_authors.book_id
    # INNER JOIN app01_author
    # ON app01_author.nid=app01_book_authors.author_id
    # GROUP BY app01_author.nid
    # ret = Book.objects.values('authors__nid').annotate(max_price=Max('price')).values('authors__name', 'max_price')
    # print(ret)
    # ret = Author.objects.values('pk').annotate(max_price=Max('book__price')).values('name', 'max_price')
    # print(ret)

    # 总结跨表的分组查询的模型：
    # 每一个后的表模型.objects.values('pk').annotate(聚合函数(关联表__统计字段)).values('表模型的所有字段以及统计字段')
    # 每一个后的表模型.objects.all().annotate(聚合函数(关联表__统计字段)).values('表模型的所有字段以及统计字段')
    # 每一个后的表模型.objects.annotate(聚合函数(关联表__统计字段)).values('表模型的所有字段以及统计字段')

    # 示例3：查询每一本书籍的名称以及对应的作者个数
    # select app01_book.title, COUNT(app01_author.name) from app01_book INNER JOIN app01_book_authors
    # ON app01_book.nid=app01_book_authors.book_id
    # INNER JOIN app01_author
    # ON app01_author.nid=app01_book_authors.author_id
    # GROUP BY app01_book.nid
    # ret = Book.objects.values('pk').annotate(c=Count('authors__name')).values('title', 'c')
    # print(ret)

    # ----- 跨表分组查询的另一种玩法：按每一行记录进行 GROUP BY ------
    # 示例1：查询每一个出版社所出版的书籍的数目
    # 等效于：ret = Publish.objects.values('nid').annotate(c=Count('book__title')).values('name','c')
    # ret = Publish.objects.all().annotate(c=Count('book__title')).values('name', 'email', 'c')
    # all() 可以被省略
    # ret = Publish.objects.annotate(c=Count('book__title')).values('name', 'email', 'c')
    # print(ret)

    # 练习
    # 统计每一个本以"红"开头的书籍的作者个数
    # ret = Book.objects.filter(title__startswith='红').values('pk').annotate(c=Count('authors__name')).values('title', 'c')
    # print(ret)
    # 列出不止一个作者的图书
    # ret = Book.objects.values('pk').annotate(c=Count('authors__name')).filter(c__gt=1).values('title', 'c')
    # print(ret)

    # ----- F查询与Q查询 ------
    # 查询评论数大于阅读数的书籍
    from django.db.models import F, Q
    # ret = Book.objects.filter(comment_num__gt=F('read_num'))
    # print(ret)
    # 给每本书加价10元
    # Book.objects.all().update(price=F('price')+10)

    # 名字为红楼梦或价格为210的书籍
    # 实际为与关系：ret = Book.objects.filter(title='红楼梦', price=110)
    # 与 & 或 | 非 ~
    ret = Book.objects.filter(Q(title='红楼梦') | Q(price=210))
    print(ret)
    # 名字不为红楼梦或价格为210的书籍
    ret = Book.objects.filter(~Q(title='红楼梦') | Q(price=210))
    print(ret)
    # 名字不为红楼梦或价格为210的书籍，且阅读数大于100
    # Q函数必须先于普通参数
    ret = Book.objects.filter(~Q(title='红楼梦') | Q(price=210), read_num__gt=100)
    print(ret)

    return HttpResponse('OK')


'''
A-B
正向查询：关联属性在A表中 A--->B
反向查询：B--->A

# 一对多查询
    正向查询：按字段
    反向查询：按表名小写_set.all()
                            book_obj.publish
    Book(关联属性：publish) --------------------> Publish
                          <--------------------
                            publish_obj.book_set.all()  # queryset
                            
     # 多对多查询
    正向查询：按字段
    反向查询：按表名小写_set.all()
                            book_obj.authors.all()
    Book(关联属性：authors) --------------------> Author
                            <--------------------  
                            author_obj.book_set.all()  # queryset    
                            
# 一对一查询
    正向查询：按字段
    反向查询：按表名小写
                            author_obj.authorDetail
    Author(关联属性：authorDetail)对象 --------------------> AuthorDetail对象
                            <--------------------  
                            authorDetail_obj.author                                                
'''
